import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.activation.DataSource;
import java.sql.*;

/**
 * Created with IDEA
 * author:ju bai
 * Date:2021/3/18
 * Time:15:17
 **/
public class TestDemo {
    public static void main1(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动程序

        Class.forName("com.mysql.jdbc.Driver");

        //2.获取连接

        String url = "jdbc:mysql://127.0.0.1:3306/test0318?useSSL=false";
        String username = "root";
        String password = "0000";
        Connection connection =  DriverManager.getConnection(url,username,password);

        //3.执行SQL语句
        String sql = "select * from user";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);

        //若有多条数据，将if改为while
        if(resultSet.next()) {
            System.out.println(resultSet.getInt(1));
            System.out.println(resultSet.getString(2));
            System.out.println(resultSet.getString(3));
        }

    }
//使用user类
    public static void main2(String[] args) throws ClassNotFoundException, SQLException {
        //1、加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");

        //2、获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/test0318?useSSL=false";
        String username = "root";//数据库的用户名称
        String password = "0000";//数据库的密码
        Connection connection =  DriverManager.getConnection(url,username,password);

        //3、执行SQL语句

        String sql = "select * from user";

        Statement statement = connection.createStatement();

        ResultSet resultSet = statement.executeQuery(sql);

        if(resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getInt(1));
            user.setName(resultSet.getString(2));
            user.setPassword(resultSet.getString(3));
            System.out.println(user);
        }
    }

//查询语句为条件查询
    public static void main3(String[] args) throws ClassNotFoundException, SQLException {
        //1、加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");

        //2、获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/test0318?useSSL=false";
        String username = "root";//数据库的用户名称
        String password = "0000";//数据库的密码
        Connection connection = DriverManager.getConnection(url,username,password);

        //3、执行SQL语句

        String sql = "select * from user where username = 'bit' and password='123'";

        Statement statement = connection.createStatement();

        ResultSet resultSet = statement.executeQuery(sql);

        if(resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getInt(1));
            user.setName(resultSet.getString(2));
            user.setPassword(resultSet.getString(3));
            System.out.println(user);
        }
    }

//查询语句的条件不固定情况
    public static void main4(String[] args) throws ClassNotFoundException, SQLException {
        //1、加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");

        //2、获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/test0318?useSSL=false";
        String username = "root";//数据库的用户名称
        String password = "0000";//数据库的密码
        Connection connection = DriverManager.getConnection(url,username,password);

        //3、执行SQL语句
        String uname = "bit";
        String upass = "123";

        String sql = "select * from user where name = '"+uname+"' and password='"+upass+"'";
        System.out.println("sql："+sql);
        Statement statement = connection.createStatement();

        ResultSet resultSet = statement.executeQuery(sql);

        if(resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getInt(1));
            user.setName(resultSet.getString(2));
            user.setPassword(resultSet.getString(3));
            System.out.println(user);
        }
    }

//sql注入
    public static void main5(String[] args) throws ClassNotFoundException, SQLException {
        //1、加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");

        //2、获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/test0318?useSSL=false";
        String username = "root";//数据库的用户名称
        String password = "0000";//数据库的密码
        Connection connection = DriverManager.getConnection(url,username,password);

        //3、执行SQL语句
        String uname = "bit";
        String upass = " 1' or '1' = '1";

        String sql = "select * from user where name = '"+uname+"' and password='"+upass+"'";
        System.out.println("sql："+sql);
        Statement statement = connection.createStatement();

        ResultSet resultSet = statement.executeQuery(sql);

        if(resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getInt(1));
            user.setName(resultSet.getString(2));
            user.setPassword(resultSet.getString(3));
            System.out.println(user);
        }
    }


//Datasource 类
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1、加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");

        //2、获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/test0318?useSSL=false";
        String username = "root";//数据库的用户名称
        String password = "0000";//数据库的密码

        //数据源
        DataSource dataSource =new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl(url);
        ((MysqlDataSource)dataSource).setUser(username);
        ((MysqlDataSource)dataSource).setPassword(password);

        //MysqlDataSource mysqlDataSource = new MysqlDataSource();

        Connection connection = dataSource.getConnection();
        //MysqlDataSource 不具备连接池的概念
        //Connection connection = DriverManager.getConnection(url,username,password);

        //3、执行SQL语句
        String uname = "bit";
        String upass = " 1' or '1' = '1";

        //String sql = "select * from user where name = '"+uname+"' and password='"+upass+"'";
        String sql = "select * from user where name = ? and password= ?";

        //Statement statement = connection.createStatement();

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,uname);
        preparedStatement.setString(2,upass);

        System.out.println("sql："+preparedStatement.toString());

        ResultSet resultSet = preparedStatement.executeQuery();

        if(resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getInt(1));
            user.setName(resultSet.getString(2));
            user.setPassword(resultSet.getString(3));
            System.out.println(user);
        }

        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

//修改语句为插入
    public static void main7(String[] args) throws ClassNotFoundException, SQLException {
        //1、加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");

        //2、获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/test0318?useSSL=false";
        String username = "root";//数据库的用户名称
        String password = "0000";//数据库的密码

        //数据源
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl(url);
        ((MysqlDataSource)dataSource).setUser(username);
        ((MysqlDataSource)dataSource).setPassword(password);

        //MysqlDataSource mysqlDataSource = new MysqlDataSource();

        Connection connection = dataSource.getConnection();
        //MysqlDataSource 不具备连接池的概念
        //Connection connection = DriverManager.getConnection(url,username,password);

        //3、执行SQL语句
        //String sql = "select * from user where name = '"+uname+"' and password='"+upass+"'";
        String sql = "insert into user(id,name,password) values (?,?,?)";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,2);
        preparedStatement.setString(2,"gaobo");
        preparedStatement.setString(3,"666");

        System.out.println("sql："+preparedStatement.toString());

        //除了 查询 其他都要用executeUpdate  返回值是受影响的行数
        int ret = preparedStatement.executeUpdate();
        if(ret != 0) {
            System.out.println("插入成功！");
        }
        preparedStatement.close();
        connection.close();
    }
//修改语句为更新
    public static void main8(String[] args) throws ClassNotFoundException, SQLException {
        //1、加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");

        //2、获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/test0318?useSSL=false";
        String username = "root";//数据库的用户名称
        String password = "0000";//数据库的密码

        //数据源
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl(url);
        ((MysqlDataSource)dataSource).setUser(username);
        ((MysqlDataSource)dataSource).setPassword(password);

        //MysqlDataSource mysqlDataSource = new MysqlDataSource();

        Connection connection = dataSource.getConnection();
        //MysqlDataSource 不具备连接池的概念
        //Connection connection = DriverManager.getConnection(url,username,password);

        //3、执行SQL语句
        //String sql = "select * from user where name = '"+uname+"' and password='"+upass+"'";
        String sql = "update user set name=? where id=?";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,"gaobo2");
        preparedStatement.setInt(2,2);

        System.out.println("sql："+preparedStatement.toString());

        //除了 查询 其他都要用executeUpdate  返回值是受影响的行数
        int ret = preparedStatement.executeUpdate();
        if(ret != 0) {
            System.out.println("更新成功！");
        }
        preparedStatement.close();
        connection.close();
    }
//修改语句为删除
    public static void main9(String[] args) throws ClassNotFoundException, SQLException {


        //2、获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/test0318?useSSL=false";
        String username = "root";//数据库的用户名称
        String password = "0000";//数据库的密码

        //数据源
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl(url);
        ((MysqlDataSource)dataSource).setUser(username);
        ((MysqlDataSource)dataSource).setPassword(password);

        //MysqlDataSource mysqlDataSource = new MysqlDataSource();

        Connection connection = dataSource.getConnection();
        //MysqlDataSource 不具备连接池的概念
        //Connection connection = DriverManager.getConnection(url,username,password);

        //3、执行SQL语句
        //String sql = "select * from user where name = '"+uname+"' and password='"+upass+"'";
        String sql = "delete from user where id=?";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,1);

        System.out.println("sql："+preparedStatement.toString());

        //除了 查询 其他都要用executeUpdate  返回值是受影响的行数
        int ret = preparedStatement.executeUpdate();
        if(ret != 0) {
            System.out.println("删除成功！");
        }
        preparedStatement.close();
        connection.close();
    }
}
